﻿using Chloe.SQLite;
using Chloe.SqlServer;
using Kang.DbDoc.Abstracts;
using Kang.DbDoc.DatabaseHelper;
using Kang.DbDoc.Domains;
using Kang.DbDoc.Enums;
using Kang.DbDoc.Interfaces;
using Kang.DbDoc.Models;
using Kang.ExtendMethod;
using Microsoft.Data.Sqlite;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Kang.DbDoc.ServicesImpl
{
    public class SQLiteConnectionService : BaseConnection, IConnectionService
    {
        public string GetConnectionString(DatabaseModel databaseModel)
        {
            var sqliteBuilder = new SqliteConnectionStringBuilder();
            sqliteBuilder.DataSource = databaseModel.DatabaseName;
            if (databaseModel.DatabasePassword.IsNotBlank())
            {
                sqliteBuilder.Password = databaseModel.DatabasePassword;
            }
            sqliteBuilder.Mode = SqliteOpenMode.ReadWriteCreate;
            return sqliteBuilder.ToString();
        }

        public void SetDefault(DatabaseModel databaseModel)
        {
            databaseModel.DatabaseUsername = "root";
            databaseModel.DatabasePort = 0;
            if (databaseModel.DatabaseHost.IsNotBlank())
            {
                databaseModel.DatabaseHost = "";
            }
        }

        public void LoadDatabaseData(DatabaseModel databaseModel, string SearchWord)
        {
            string sql = @"
                SELECT
                    a.name AS 'table_name', 
                    '' AS 'table_comment', 
                    a.type AS 'table_type'
                FROM        
                    sqlite_master a
                WHERE         
                    type='table' AND a.name NOT LIKE 'sqlite_%' AND a.name LIKE '%" + SearchWord.StringTrim() + "%';";

            string sqlViewsInfo = @"
                SELECT
                	a.name,
                	'' as 'view_definition'
                FROM
                	sqlite_master a
                WHERE
                	type='view' AND a.name NOT LIKE 'sqlite_%' AND a.name LIKE '%" + SearchWord.StringTrim() + "%' " + @"
                ORDER BY
                	a.name ASC;";
            using (var my = new SQLiteContext(new SQLiteConnectionFactory(databaseModel.ConnectionString)))
            {
                var tableList = my.SqlQuery<DbTable>($"{sql.StringTrim()}")
                .ToList();
                var viewList = my.SqlQuery<DbView>($"{sqlViewsInfo.StringTrim()}")
                .ToList();
                tableList.ForEach(x => 
                {
                    var item = new TableOrViewModel()
                    {
                        Name = x.Name,
                        IsCheck = false,
                        Type = GetTableType(x.Type),
                        Comment = x.Comment,
                    };

                    var Columns = my.SqlQuery<DbColumnSqlite>($@"PRAGMA table_info('{x.Name}');").Select(c => new DbColumn
                    {
                        Name = c.Name,
                        Type = c.Type,
                        ColumnNull = c.NotNull == 1 ? "0" : "1",
                        ColumnKeyType = c.PK == 1 ? "PRI" : "",
                        ColumnDefault = c.ColumnDefault,
                    }).ToList();
                    foreach (var c in Columns)
                    {
                        item.Columns.Add(new ColumnModel()
                        {
                            Name = c.Name,
                            Type = c.Type,
                            Length = c.ColumnLength,
                            Scale = c.ColumnScale,
                            KeyType = c.ColumnKeyType,
                            Extra = c.ColumnExtra,
                            IsAutoIncrement = c.ColumnExtra == "1",
                            IsNull = c.ColumnNull == "1",
                            Default = c.ColumnDefault
                        });
                    }
                    App.Current.Dispatcher.Invoke(() =>
                    {
                        switch (item.Type)
                        {
                            case TableTypeEnum.Table:
                                databaseModel.Tables.Add(item);
                                break;
                            case TableTypeEnum.View:
                                item.ViewDefiner = viewList.Where(v => v.Name == item.Name).Select(v => v.Definer).FirstOrDefault();
                                item.ViewDefinition = viewList.Where(v => v.Name == item.Name).Select(v => v.ViewDefinition).FirstOrDefault();
                                databaseModel.Views.Add(item);
                                break;
                        }
                    });
                });
            }
        }
    }
}
